package org.lq.sm.dao.impl;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import org.lq.sm.dao.GoodsDao;
import org.lq.sm.entity.Goods;
import org.lq.sm.util.Instantiation;
import org.lq.sm.util.JDBCUtil;


/**
 * 商品功能实现类
 * @author 3组 李淑帆
 *
 */
public class GoodsDaoImpl implements GoodsDao,Instantiation<Goods> {


	/**
	 * 添加保存信息
	 */
	@Override
	public int save(Goods t) {
		String sql = "insert into goods(`goods_number`, `name`, `code`, `icon`, `category`, `brand`, `unit`, `purchase_price`, `sell_price`, `trade_price`, `cost_price`, `inventory`, `integral`, `status`, `max_inventory`, `min_inventory`, `purchase_num`, `sell_num`,manuName,manuAddress,note)"
				+ "values"
				+ "(?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?,?)";
		int num = 0;
		try {
			num = JDBCUtil.executeUpdate(sql, t.getGoods_number(),t.getName(),t.getCode(),t.getIcon(),
					t.getCategory(),t.getBrand(),t.getUnit(),t.getPurchase_price(),
					t.getSell_price(),t.getTrade_price(),t.getCost_price(),t.getInventory(),
					t.getIntegral(),t.getStatus(),t.getMax_inventory(),t.getMin_inventory(),
					t.getPurchase_num(),t.getSell_num(),t.getManuName(),t.getManuAddress(),t.getNote());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	/**
	 * 修改信息
	 */
	@Override
	public int update(Goods t) {
		String sql = "update goods set goods_number = ?,name = ?,code=?,category=?,"
				+ "brand=?,unit=?,purchase_price=?,sell_price=?,trade_price=?,"
				+ "cost_price=?,inventory=?,integral=?,status = ?,max_inventory=?,"
				+ "min_inventory=?,purchase_num=?,sell_num=?, icon = ? ,manuName=?,manuAddress=?,note=? where id = ?";
		int num = 0;
		try {
			num = JDBCUtil.executeUpdate(sql,  t.getGoods_number(),t.getName(),t.getCode(),
					t.getCategory(),t.getBrand(),t.getUnit(),t.getPurchase_price(),
					t.getSell_price(),t.getTrade_price(),t.getCost_price(),t.getInventory(),
					t.getIntegral(),t.getStatus(),t.getMax_inventory(),t.getMin_inventory(),
					t.getPurchase_num(),t.getSell_num(),t.getIcon(),t.getManuName(),t.getManuAddress(),t.getNote(),t.getId());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return num;
	}

	/**
	 * 根据编号删除
	 */
	@Override
	public int delete(int id) {
		String sql = "delete from goods where id = ?";
		int num = 0;
		try {
			num = JDBCUtil.executeUpdate(sql, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}	
		return num;
	}

	/**
	 * 查询全部
	 */
	@Override
	public List<Goods> findAll() {
		String sql = "select * from goods";
		List<Goods> list = new ArrayList<Goods>();
		try {
			list = JDBCUtil.executeQuery(sql, this);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}


	/**
	 * 根据id查询
	 */
	@Override
	public Goods getById(int id) {
		String sql = "select * from goods where id = ?";
		List<Goods> list = new ArrayList<Goods>();
		try {
			list = JDBCUtil.executeQuery(sql, this, id);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list.get(0);//集合中只有一个元素，将他取出
	}

	/**
	 * 根据商品名称模糊查询
	 */
	@Override
	public List<Goods> getByName(String name) {
		String sql = "select * from goods where name like ?";
		List<Goods> list = new ArrayList<Goods>();
		try {
			list = JDBCUtil.executeQuery(sql, this,"%"+name+"%");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public Goods instance(ResultSet rs) {
		Goods goods = new Goods();
		try {
			goods.setId(rs.getInt("id"));
			goods.setGoods_number(rs.getString("goods_number"));
			goods.setName(rs.getString("name"));
			goods.setCode(rs.getString("code"));
			goods.setCategory(rs.getInt("category"));
			goods.setBrand(rs.getInt("brand"));
			goods.setUnit(rs.getInt("unit"));
			goods.setPurchase_price(rs.getDouble("purchase_price"));
			goods.setSell_price(rs.getDouble("sell_price"));
			goods.setTrade_price(rs.getDouble("trade_price"));
			goods.setCost_price(rs.getDouble("cost_price"));
			goods.setInventory(rs.getInt("inventory"));
			goods.setIntegral(rs.getInt("integral"));
			goods.setStatus(rs.getInt("status"));
			goods.setMax_inventory(rs.getInt("max_inventory"));
			goods.setMin_inventory(rs.getInt("min_inventory"));
			goods.setPurchase_num(rs.getInt("purchase_num"));
			goods.setSell_num(rs.getInt("sell_num"));
			goods.setManuName(rs.getString("manuName"));
			goods.setManuAddress(rs.getString("manuAddress"));
			goods.setNote(rs.getString("note"));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return goods;
	}

	@Override
	public List<Goods> findGoodsByCategoryID(int cid) {
		try {
			return JDBCUtil.executeQuery("select * from goods where category = ?", this, cid);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	@Override
	public List<Goods> getByPinYin(String name) {
		String sql = "select * from goods where code like ?";
		List<Goods> list = new ArrayList<Goods>();
		try {
			list = JDBCUtil.executeQuery(sql, this,"%"+name.toUpperCase()+"%");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}

	@Override
	public List<Goods> getByNumber(String name) {
		String sql = "select * from goods where goods_number like ?";
		List<Goods> list = new ArrayList<Goods>();
		try {
			list = JDBCUtil.executeQuery(sql, this,"%"+name+"%");
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return list;
	}


}
